package com.rankeiot.core.excel;


import ch.rabanti.nanoxlsx4j.Workbook;
import ch.rabanti.nanoxlsx4j.Worksheet;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.rankeiot.core.Context;
import com.rankeiot.core.anno.Excel;
import com.rankeiot.core.data.Item;
import com.rankeiot.core.translator.FormatTranslator;
import com.rankeiot.core.translator.MultipleTranslator;
import com.rankeiot.core.translator.SimpleTranslator;
import com.rankeiot.core.translator.Translator;
import com.rankeiot.core.util.StringUtil;

import lombok.SneakyThrows;

import org.sagacity.sqltoy.SqlToyContext;
import org.sagacity.sqltoy.dao.SqlToyLazyDao;
import org.sagacity.sqltoy.utils.BeanUtil;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.function.Consumer;


/**
 * 数据导入导出工具
 * 先做excel，考虑下DBF
 */
public class ExcelUtil {
    private static SqlToyContext _sqlToyContext;


    private synchronized static SqlToyContext getSqlToyContext(){
        if(_sqlToyContext==null){
            _sqlToyContext= Context.getBean(SqlToyContext.class);
        }
        return _sqlToyContext;
    }

    /**
     * 简单的按字段导出
     *
     * @param dataList
     * @param translators
     * @param outputStream
     */
    public static void exportExcel(List dataList, Map<String, Translator> translators, OutputStream outputStream) {
        if (dataList == null || dataList.size() == 0 || dataList.get(0) == null) {
            return;
        }
        Class exportDef = dataList.get(0).getClass();
        List<Item> titles = new ArrayList<>();
        int order = 0;
        for (Field field : exportDef.getDeclaredFields()) {
            Item title = new Item();
            title.setKey(field.getName());
            title.setTitle(field.getName());
            title.setOrder(order++);
            titles.add(title);
        }
        Collections.sort(titles);
        Workbook workbook = new Workbook("Sheet1");
        Worksheet ws = workbook.getCurrentWorksheet();
        for (Item title : titles) {
            ws.addNextCell(title.getTitle());
        }
        boolean isMap = false;
        try (OutputStream out = outputStream) {
            for (Object data : dataList) {
                ws.goToNextRow();
                for (Item title : titles) {
                    Object value = null;
                    if (isMap || data instanceof Map) {
                        isMap = true;
                        value = ((Map) data).get(title.getKey());
                    } else {
                        try {
                            value = BeanUtil.getProperty(data, title.getKey());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    if (value == null) {
                        int c = ws.getCurrentColumnNumber();
                        ws.setCurrentColumnNumber(c + 1);
                    } else {
                        Translator translator = translators.get(title.getKey());
                        if (translator != null) {
                            ws.addNextCell(translator.translate(data, title.getKey(), value));
                        } else {
                            ws.addNextCell(value);
                        }
                    }
                }
            }
            workbook.saveAsStream(out);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ch.rabanti.nanoxlsx4j.exceptions.IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel到输出流
     *
     * @param dao
     * @param sqlId
     * @param bean         查询参数及导出模板
     * @param outputStream 输出流
     */
    public static void exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, OutputStream outputStream) {
        exportExcel(dao, sqlId, bean, new HashMap<>(), outputStream);
    }

    /**
     * @param dataList     需要导出的数据
     * @param exportDef    导出数据格式定义
     * @param translators  翻译器,key对应字段名
     * @param outputStream 导出到输出流
     */
    public static void exportExcel(List dataList, Class exportDef, Map<String, Translator> translators, OutputStream outputStream) {
        //获得导出字段及表头
        List<Item> titles = new ArrayList<>();
        Set<String> fields = new HashSet<>();
        for (Field field : exportDef.getDeclaredFields()) {
            Excel export = field.getAnnotation(Excel.class);
            if (export == null) {
                continue;
            }
            if (!export.out()) {
                fields.add(field.getName());
                continue;
            }
            Item title = new Item();
            title.setKey(field.getName());
            title.setTitle(field.getName());
            if (StringUtil.isNotEmpty(export.value())) {
                title.setTitle(export.value());
            }
            title.setOrder(export.order());
            titles.add(title);
            fields.add(field.getName());
            //使用本身translate,不再额外处理,只处format理格式转换
            if (!translators.containsKey(title.getKey())) {
                Translator translator = null;
                JsonFormat jf = field.getAnnotation(JsonFormat.class);
                if (jf != null && StringUtil.isNotEmpty(jf.pattern())) {
                    translator = new FormatTranslator(jf.pattern());
                    translators.put(title.getKey(), translator);
                }
            }
        }
        //父级,最多处理两级
        for (Field field : exportDef.getSuperclass().getDeclaredFields()) {
            Excel export = field.getAnnotation(Excel.class);
            if (export == null) {
                continue;
            }
            if (!export.out()) {
                continue;
            }
            if (fields.contains(field.getName())) {
                continue;
            }
            Item title = new Item();
            title.setKey(field.getName());
            title.setTitle(field.getName());
            if (StringUtil.isNotEmpty(export.value())) {
                title.setTitle(export.value());
            }
            title.setOrder(export.order());
            titles.add(title);
            if (!translators.containsKey(title.getKey())) {
                Translator translator = null;
                JsonFormat jf = field.getAnnotation(JsonFormat.class);
                if (jf != null && StringUtil.isNotEmpty(jf.pattern())) {
                    translator = new FormatTranslator(jf.pattern());
                    translators.put(title.getKey(), translator);
                }
            }
        }
        Collections.sort(titles);

        Workbook workbook = new Workbook("Sheet1");
        Worksheet ws = workbook.getCurrentWorksheet();
        for (Item title : titles) {
            ws.addNextCell(title.getTitle());
        }
        Boolean isMap = null;
        try (OutputStream out = outputStream) {
            for (Object data : dataList) {
                ws.goToNextRow();
                for (Item title : titles) {
                    Object value = null;
                    if (isMap == null) {
                        isMap = data instanceof Map;
                    }
                    if (isMap) {
                        value = ((Map) data).get(title.getKey());
                    } else {
                        try {
                            value = BeanUtil.getProperty(data, title.getKey());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    if (value == null) {
                        int c = ws.getCurrentColumnNumber();
                        ws.setCurrentColumnNumber(c + 1);
                    } else {
                        Translator translator = translators.get(title.getKey());
                        if (translator != null) {
                            ws.addNextCell(translator.translate(data, title.getKey(), value));
                        } else {
                            ws.addNextCell(value);
                        }
                    }
                }
            }
            workbook.saveAsStream(out);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ch.rabanti.nanoxlsx4j.exceptions.IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel到输出流
     *
     * @param dao
     * @param sqlId
     * @param bean         查询参数及导出模板
     * @param translators  转翻器,用于自定义输出字段的值，可以是字段转换，格式话等，map的key值为bean中需要转换的字段名
     * @param outputStream 输出流
     */
    public static void exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, Map<String, Translator> translators, OutputStream outputStream) {
//        QueryExecutor qe = new QueryExecutor(sqlId, bean);
//        qe.resultType(Map.class);
        List dataList = dao.findBySql(sqlId, bean);
        //new QueryExecutor(sqlId,bean).getInnerModel().
       //dao.query().sql(sqlId).entity(bean).rowhandler().find();
        exportExcel(dataList, bean.getClass(), translators, outputStream);
    }


    /**
     * @param dao
     * @param sqlId
     * @param bean     查询参数及导出模板
     * @param fileName 导出文件名
     */
    public static ResponseEntity<StreamingResponseBody> exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, String fileName) {
        return exportExcel(dao, sqlId, bean, new HashMap<>(), fileName);
    }

    /**
     * @param dao
     * @param sqlId
     * @param bean     查询参数及导出模板
     * @param fileName 导出文件名
     */
    public static ResponseEntity<StreamingResponseBody> exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, Map<String, Translator> translators, String fileName) {
        try {
           return ResponseEntity.ok()
                    .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
                    .header("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx;")
                    .body(out->{
                        exportExcel(dao, sqlId, bean, translators, out);
                    });
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }
    public static <T> List<T> importExcel(InputStream in, Class<T> type) {
        return importExcel(in, type, new HashMap<>());
    }

    /**
     * @param in
     * @param type        需要转换出来的bean类型
     * @param translators 转译器,用于值定义翻译对应的字段,key值为bean的name而非表格中的title
     * @param <T>
     * @return
     */
    @SneakyThrows
    public static <T> List<T> importExcel(InputStream in, Class<T> type, Map<String, Translator> translators) {
        List<T> result = new ArrayList<>();
        TypedRowHandler th = new TypedRowHandler(type, translators, o -> {
            result.add((T) o);
        });
        ExcelLoader.load(in, th);
        return result;
    }
    /**
     * 导入Excel,可以导入大文件
     * @param in
     * @param type        需要转换出来的bean类型
     * @param translators 转译器,用于值定义翻译对应的字段,key值为bean的name而非表格中的title
     * @param <T>
     * @return
     */
    @SneakyThrows
    public static <T> void importExcel(InputStream in, Class<T> type, Map<String, Translator> translators, Consumer<T> objHalder) {
        TypedRowHandler th = new TypedRowHandler(type, translators, objHalder);
        ExcelLoader.load(in, th);
    }

    //

    //反向翻译
    public static Translator getUnTranslator(String name, String type, String split) {
        if (getSqlToyContext() == null) {
            return Translator.DEFAULT;
        }
        HashMap<String, Object[]> data = getSqlToyContext().getTranslateManager().getCacheData(name, StringUtil.isEmpty(type) ? null : type);
        Map<Object, Object> transMap = new HashMap<>();
        for (Object[] dt : data.values()) {
            transMap.put(dt[1], dt[0]);
        }
        return StringUtil.isNotEmpty(split) ? new MultipleTranslator(transMap, split) : new SimpleTranslator(transMap);
    }

    /**
     * 正向翻译
     *
     * @param name
     * @param type
     * @param multi
     * @return
     */
    public static Translator getTranslator(String name, String type, boolean multi) {
        if (getSqlToyContext() == null) {
            return Translator.DEFAULT;
        }
        HashMap<String, Object[]> data = getSqlToyContext().getTranslateManager().getCacheData(name, StringUtil.isEmpty(type) ? null : type);
        Map<Object, Object> transMap = new HashMap<>();
        for (Object[] dt : data.values()) {
            transMap.put(dt[0], dt[1]);
        }
        return multi ? new MultipleTranslator(transMap) : new SimpleTranslator(transMap);
    }
}
